/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Bean.SchedulingInterviewBean;
import Bean.VacancyBean;
import Utility.DBProcess;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Vietlt
 */
public class VacancyDAO {

    public static void main(String[] args) {
//        VacancyBean vacancyBean = new VacancyDAO().LoadAllVacancyByID(1);
//        System.out.println("EmployeeID:" + vacancyBean.getPosition());
//        System.out.println("EmployeeID:" + vacancyBean.getWorkType());
//
//
//        System.out.println("--------");
//        VacancyBean va = new VacancyBean();
//        va.setDepartmentID(1);
//        va.setTitle("as");
//        va.setCreatedDate("11/11/1999");
//        va.setStatus(2);
//        va.setNumberRequirement(2);
//        va.setPosition("adsa");
//        va.setWorkingPlace("sá");
//        va.setWorkType("adas");
//        va.setMinimumSalary(1333);
//        va.setMaximumSalary(211212);
//        va.setDescription("dấd");
//        va.setSkillRequirement("adas");
//        va.setEntitlement("đá");
//        va.setMinimumAge(21);
//        va.setMaximumAge(32);
//        va.setGender(true);
//        va.setDegree("sdas");
//        va.setYearOfExperience("11/11/1999");
//        va.setProbationaryPeriod("ádfsf");
//        va.setDeadline("11/11/1999");
//        
//        
int n=new VacancyDAO().DeleteVacancy(1);
if(n>0)
{
    System.out.println("de ok");
}
    }

    public List<VacancyBean> LoadAllVacancy() {
        List<VacancyBean> list = new ArrayList<VacancyBean>();
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        try {
            stm = conn.prepareCall("{ call Vacancy_getall}");
            rs = stm.executeQuery();
            while (rs.next()) {
                VacancyBean va = new VacancyBean();
                va.setVacancyID(rs.getInt("VacancyID"));
                va.setDepartmentID(rs.getInt("DepartmentID"));
                va.setTitle(rs.getString("Title"));
                va.setCreatedDate(rs.getDate("CreatedDate").toString());
                va.setStatus(rs.getInt("Status"));
                va.setNumberRequirement(rs.getInt("NumberRequirement"));
                va.setPosition(rs.getString("Position"));
                va.setWorkingPlace(rs.getString("WorkingPlace"));
                va.setWorkType(rs.getString("WorkType"));
                va.setMinimumSalary(rs.getInt("MinimumSalary"));
                va.setMaximumSalary(rs.getInt("MaximumSalary"));
                va.setDescription(rs.getString("Description"));
                va.setSkillRequirement(rs.getString("SkillRequirement"));
                va.setEntitlement(rs.getString("Entitlement"));
                va.setMinimumAge(rs.getInt("MinimumAge"));
                va.setMaximumAge(rs.getInt("MaximumAge"));
                va.setGender(rs.getBoolean("Gender"));
                va.setDegree(rs.getString("Degree"));
                va.setYearOfExperience(rs.getString("YearOfExperience"));
                va.setProbationaryPeriod(rs.getString("ProbationaryPeriod"));
                va.setDeadline(rs.getString("Deadline"));
                list.add(va);
                if (list.isEmpty()) {
                    String err = "List empty !!!";
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(ApplicantDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return list;
    }

    public VacancyBean LoadAllVacancyByID(int VacancyID) {
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        VacancyBean va = new VacancyBean();
        try {
            stm = conn.prepareCall("{ call Vacancy_get (?)}");
            stm.setInt(1, VacancyID);
            rs = stm.executeQuery();
            while (rs.next()) {
                va.setVacancyID(rs.getInt("VacancyID"));
                va.setDepartmentID(rs.getInt("DepartmentID"));
                va.setTitle(rs.getString("Title"));
                va.setCreatedDate(rs.getDate("CreatedDate").toString());
                va.setStatus(rs.getInt("Status"));
                va.setNumberRequirement(rs.getInt("NumberRequirement"));
                va.setPosition(rs.getString("Position"));
                va.setWorkingPlace(rs.getString("WorkingPlace"));
                va.setWorkType(rs.getString("WorkType"));
                va.setMinimumSalary(rs.getInt("MinimumSalary"));
                va.setMaximumSalary(rs.getInt("MaximumSalary"));
                va.setDescription(rs.getString("Description"));
                va.setSkillRequirement(rs.getString("SkillRequirement"));
                va.setEntitlement(rs.getString("Entitlement"));
                va.setMinimumAge(rs.getInt("MinimumAge"));
                va.setMaximumAge(rs.getInt("MaximumAge"));
                va.setGender(rs.getBoolean("Gender"));
                va.setDegree(rs.getString("Degree"));
                va.setYearOfExperience(rs.getString("YearOfExperience"));
                va.setProbationaryPeriod(rs.getString("ProbationaryPeriod"));
                va.setDeadline(rs.getString("Deadline"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ApplicantDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return va;
    }

    public int AddNewVacancy(VacancyBean sc) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        int isComplete = 0;
        try {
            stm = conn.prepareCall("{ call Vacancy_add(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setInt(1, sc.getDepartmentID());
            stm.setString(2, sc.getTitle());
            String dob = sc.getCreatedDate();
            SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
            Date date = format.parse(dob);
            stm.setDate(3, new java.sql.Date(date.getTime()));
            stm.setInt(4, sc.getStatus());
            stm.setInt(5, sc.getNumberRequirement());
            stm.setString(6, sc.getPosition());
            stm.setString(7, sc.getWorkingPlace());
            stm.setString(8, sc.getWorkType());
            stm.setInt(9, sc.getMinimumSalary());
            stm.setInt(10, sc.getMaximumSalary());
            stm.setString(11, sc.getDescription());
            stm.setString(12, sc.getSkillRequirement());
            stm.setString(13, sc.getEntitlement());
            stm.setInt(14, sc.getMinimumAge());
            stm.setInt(15, sc.getMaximumAge());
            stm.setBoolean(16, sc.isGender());
            stm.setString(17, sc.getDegree());
            String dob1 = sc.getYearOfExperience();
            Date date1 = format.parse(dob);
            stm.setDate(18, new java.sql.Date(date1.getTime()));
            stm.setString(19, sc.getProbationaryPeriod());
            String dob2 = sc.getDeadline();
            Date date2 = format.parse(dob2);
            stm.setDate(20, new java.sql.Date(date2.getTime()));

            if (stm.executeUpdate() > 0) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return isComplete;
    }
            
    public int UpdateVacancy(VacancyBean sc) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        int isComplete = 0;
        try {
            stm = conn.prepareCall("{ call Vacancy_update(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setInt(1, sc.getVacancyID());
            stm.setInt(2, sc.getDepartmentID());
            stm.setString(3, sc.getTitle());
            String dob = sc.getCreatedDate();
            SimpleDateFormat simp = new SimpleDateFormat("dd/MM/yyy");
            Date datime = simp.parse(dob);
            stm.setDate(4, new java.sql.Date(datime.getTime()));
            stm.setInt(5, sc.getStatus());
            stm.setInt(6, sc.getNumberRequirement());
            stm.setString(7, sc.getPosition());
            stm.setString(8, sc.getWorkingPlace());
            stm.setString(9, sc.getWorkType());
            stm.setInt(10, sc.getMinimumSalary());
            stm.setInt(11, sc.getMaximumSalary());
            stm.setString(12, sc.getDescription());
            stm.setString(13, sc.getSkillRequirement());
            stm.setString(14, sc.getEntitlement());
            stm.setInt(15, sc.getMinimumAge());
            stm.setInt(16, sc.getMaximumAge());
            stm.setBoolean(17, sc.isGender());
            stm.setString(18, sc.getDegree());
            String dob1 = sc.getYearOfExperience();
            SimpleDateFormat simp1 = new SimpleDateFormat("dd/MM/yyy");
            Date datime1 = simp1.parse(dob1);
            stm.setDate(19, new java.sql.Date(datime1.getTime()));
            stm.setString(20, sc.getProbationaryPeriod());
            java.sql.Date dob2 = java.sql.Date.valueOf(sc.getDeadline());
            stm.setDate(21, dob2);

            if (stm.executeUpdate() > 0) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return isComplete;
    }

      public int DeleteVacancy(int VacancyID) {
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        //ApplicantBean upB=new ApplicantBean();
        int isComplete = 0;
        try {
            stm = conn.prepareCall("{ call Vacancy_delete (?)}");
            stm.setInt(1, VacancyID);
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ApplicantDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return isComplete;
    }

}
